import os
import win32com.client
import pythoncom
import time
import re
import tkinter as tk
from tkinter import filedialog, messagebox
import json

def connect_to_cad():
    """连接到正在运行的AutoCAD实例"""
    try:
        cad = win32com.client.Dispatch("AutoCAD.Application")
        print("已连接到AutoCAD")
        return cad
    except Exception as e:
        print(f"连接AutoCAD失败: {e}")
        return None

def get_active_document(cad):
    """获取活动文档"""
    try:
        doc = cad.ActiveDocument
        print("已获取活动文档")
        return doc
    except Exception as e:
        print(f"获取活动文档失败: {e}")
        return None

# 全局变量用于跟踪我们创建的Excel资源
excel_app = None
opened_workbooks = {}  # 记录我们自己打开的工作簿 {文件路径: 工作簿对象}
formula_info_var = None  # 新增：公式信息输入框变量

def get_excel_application():
    """获取或创建Excel应用实例"""
    global excel_app
    if excel_app is None:
        try:
            # 创建Excel应用对象，确保不显示任何界面和警告
            excel_app = win32com.client.Dispatch("Excel.Application")
            excel_app.Visible = False
            excel_app.DisplayAlerts = False
            excel_app.AskToUpdateLinks = False
            excel_app.AlertBeforeOverwriting = False
            print("创建了新的Excel应用实例")
        except Exception as e:
            print(f"创建Excel应用失败: {e}")
            return None
    return excel_app

def read_excel_formulas(excel_file, sheet_name, cell_range):
    """从Excel中读取指定范围的单元格公式及相关内容"""
    global excel_app, opened_workbooks, formula_info_var
    
    try:
        excel_file = os.path.abspath(excel_file)
        excel = get_excel_application()
        if not excel:
            raise Exception("无法获取Excel应用实例")
                
        # 检查文件是否已经打开（由用户或其他程序打开）
        workbook = None
        is_workbook_opened_by_us = False
        
        # 遍历所有打开的工作簿
        for wb in excel.Workbooks:
            try:
                if os.path.abspath(wb.FullName).lower() == excel_file.lower():
                    workbook = wb
                    print(f"文件 {excel_file} 已被打开，直接使用")
                    # 检查是否是我们自己打开的
                    if excel_file in opened_workbooks:
                        is_workbook_opened_by_us = True
                    break
            except Exception as e:
                print(f"检查工作簿时出错: {e}")
                continue
        
        # 如果文件未被打开， ourselves打开它
        if workbook is None:
            try:
                workbook = excel.Workbooks.Open(
                    excel_file,
                    ReadOnly=True,
                    UpdateLinks=0,
                    IgnoreReadOnlyRecommended=True
                )
                opened_workbooks[excel_file] = workbook
                is_workbook_opened_by_us = True
                print(f"以只读方式打开工作簿: {excel_file}")
            except Exception as e:
                print(f"打开Excel文件失败: {e}")
                raise Exception(f"无法打开Excel文件: {e}")
        
        # 获取工作表
        try:
            sheet = workbook.Sheets(sheet_name)
            print(f"获取工作表: {sheet_name}")
            range_obj = sheet.Range(cell_range)
        except Exception as e:
            print(f"获取工作表失败: {e}")
            # 确保不会关闭用户打开的文件
            if excel_file in opened_workbooks:  # 只处理 ourselves打开的文件
                try:
                    workbook.Close(False)
                except:
                    pass
                del opened_workbooks[excel_file]
            raise Exception(f"无法获取工作表 {sheet_name}: {e}")

        # 处理单元格数据
        cell_ref_pattern = re.compile(r'\$?([A-Z]+)\$?(\d+)')
        formulas = []
        
        def get_full_formula(cell, depth=0):
            """递归获取完整公式链，根据回溯模式控制深度"""
            if not cell.Formula.startswith('='):
                return str(cell.Value) if cell.Value is not None else ""
                
            # 仅前一个单元格模式：只递归一层
            if traceback_mode_var.get() == "previous" and depth >= 1:
                return str(cell.Value) if cell.Value is not None else ""  # 返回数值
            
            formula = cell.Formula[1:]
            refs = re.findall(r'\$?[A-Za-z]+\$?\d+', formula)
            
            # 替换所有引用为它们的值或完整公式
            for ref in set(refs):
                try:
                    ref_cell = sheet.Range(ref)
                    ref_value = get_full_formula(ref_cell, depth + 1)  # 递归时深度+1
                    # 仅当回溯模式为all且引用是公式时才添加括号（关键修改）
                    if traceback_mode_var.get() == "all" and ref_cell.Formula.startswith('='):
                        ref_value = f"({ref_value})"
                    formula = formula.replace(ref, ref_value)
                except:
                    formula = formula.replace(ref, "N/A")
            
            return formula

        for row in range_obj:
            for cell in row:
                # 获取上单元格的值（优先使用用户输入的公式信息）
                user_formula_info = formula_info_var.get() if hasattr(formula_info_var, 'get') else ""
                if user_formula_info:
                    # 检查是否是单元格范围格式（如A1:M1）
                    if re.match(r'^[A-Za-z]+\d+:[A-Za-z]+\d+$', user_formula_info):
                        start_cell, end_cell = user_formula_info.split(':')
                        start_col = re.match(r'([A-Za-z]+)', start_cell).group(1)
                        end_col = re.match(r'([A-Za-z]+)', end_cell).group(1)
                        
                        # 获取当前单元格列字母
                        current_col = re.match(r'([A-Za-z]+)', cell.Address.replace('$','')).group(1)
                        
                        # 如果当前列在范围内，则使用对应单元格的值
                        if start_col <= current_col <= end_col:
                            ref_cell = f"{current_col}{re.match(r'[A-Za-z]+(\d+)', start_cell).group(1)}"
                            try:
                                ref_value = sheet.Range(ref_cell).Value
                                above_part = str(ref_value) if ref_value is not None else ""
                            except:
                                above_part = ""
                        else:
                            above_part = ""
                    else:
                        # 不是范围格式，直接使用输入的内容
                        above_part = user_formula_info
                else:
                    # 用户没有输入公式信息，使用上方单元格的值
                    above_cell = sheet.Cells(cell.Row - 1, cell.Column)
                    above_cell_value = above_cell.Value
                    if above_cell_value is None or above_cell_value == "":
                        above_part = ""
                    elif isinstance(above_cell_value, (int, float)):
                        above_part = "{:.2f}".format(above_cell_value)
                    else:
                        above_part = str(above_cell_value)

                # 获取当前单元格的完整公式链
                full_formula = get_full_formula(cell)
                
                # 获取当前单元格的值
                cell_value = cell.Value
                if cell_value is None or cell_value == "":
                    cell_part = ""
                elif isinstance(cell_value, (int, float)):
                    cell_part = "{:.2f}".format(cell_value)
                else:
                    cell_part = str(cell_value)

                # 组合内容 - 只有当单元格有值时才显示等号和值
                if cell_part:
                    if above_part:
                        combined_content = f"{above_part}:{full_formula}={cell_part}"
                    else:
                        combined_content = f"{full_formula}={cell_part}"
                else:
                    if above_part:
                        combined_content = f"{above_part}:{full_formula}"
                    else:
                        combined_content = full_formula
                
                formulas.append(combined_content)
        
        # 替换特殊符号
        formulas = [formula.replace('*', 'x').replace('/', '/') for formula in formulas]
        
        print(f"从Excel成功读取了{len(formulas)}个公式")
        return formulas
    except Exception as e:
        print(f"从Excel读取公式失败: {e}")
        return []

def insert_text_to_cad(doc, text_content, insertion_point, height=350):
    """在CAD中插入单行文本"""
    try:
        model_space = doc.ModelSpace
        text = model_space.AddText(text_content, insertion_point, height)
        text.Layer = "Text"
        text.Color = 2
        text.StyleName = "HZ"
        print(f"已插入文本: {text_content}")
        return text
    except Exception as e:
        print(f"插入文本失败: {e}")
        return None

def get_user_selected_point(doc, cell_range, remark):
    """让用户在CAD中选择一个点，增加重试机制"""
    max_retries = 3
    retry_count = 0
    
    while retry_count < max_retries:
        try:
            prompt_message = f"请选择 {remark} (范围:{cell_range}) 的插入点位置...\n(请在CAD窗口点击选择位置)"
            doc.Utility.Prompt(prompt_message)
            # 完全移除窗口状态设置代码
            point = doc.Utility.GetPoint()
            if point:
                return point
        except Exception as e:
            print(f"获取用户选择点失败(尝试 {retry_count + 1}/{max_retries}): {e}")
            retry_count += 1
            time.sleep(0.5)  # 短暂等待后重试
    
    print(f"无法获取插入点，已达到最大重试次数 {max_retries}")
    return None

def cleanup_excel_resources():
    """清理Excel资源，只关闭 ourselves打开的文件"""
    global excel_app, opened_workbooks
    
    try:
        print("开始清理Excel资源...")
        if excel_app:
            # 只关闭 ourselves打开的工作簿
            for file_path, workbook in list(opened_workbooks.items()):
                try:
                    if workbook:
                        workbook.Close(False)
                        print(f"已关闭工作簿: {file_path}")
                except Exception as e:
                    print(f"关闭工作簿 {file_path} 失败: {e}")
                finally:
                    if file_path in opened_workbooks:
                        del opened_workbooks[file_path]
            
            # 重要：不退出Excel应用，因为可能有用户打开的文件
            # excel_app.Quit()  # 明确不调用这个方法
        
        print("Excel资源清理完成")
    except Exception as e:
        print(f"清理Excel资源失败: {e}")
    finally:
        opened_workbooks.clear()

def main():
    global formula_info_var, traceback_mode_var  # 新增全局变量声明
    
    # 创建 GUI 窗口
    root = tk.Tk()
    root.title("CAD插入公式V2.0-by熊本熊本熊本熊")

    # 定义变量
    excel_file_var = tk.StringVar()
    sheet_name_var = tk.StringVar(value="Sheet1")
    cell_range_var = tk.StringVar(value="B1")
    text_height_var = tk.StringVar(value="350")
    direction_var = tk.StringVar(value="vertical")
    cached_formulas = {}  # 新增：缓存读取的公式数据 {cell_range: formulas}
    formula_info_var = tk.StringVar()  # 初始化全局变量

    def select_excel_file():
        file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx;*.xls")])
        if file_path:
            excel_file_var.set(file_path)

    def export_settings():
        settings = {
            "excel_file": excel_file_var.get(),
            "sheet_name": sheet_name_var.get(),
            "cell_range": cell_range_var.get(),
            "text_height": text_height_var.get(),
            "direction": direction_var.get(),
            "formula_info": formula_info_var.get(),  # 新增：公式信息
            "row_filter": row_filter_var.get()       # 新增：行过滤选项
        }
        file_path = filedialog.asksaveasfilename(defaultextension=".json", filetypes=[("JSON files", "*.json")])
        if file_path:
            try:
                with open(file_path, 'w') as f:
                    json.dump(settings, f)
                messagebox.showinfo("成功", "设置已导出")
            except Exception as e:
                messagebox.showerror("错误", f"导出设置失败: {e}")
    
    def import_settings():
        file_path = filedialog.askopenfilename(filetypes=[("JSON files", "*.json")])
        if file_path:
            try:
                with open(file_path, 'r') as f:
                    settings = json.load(f)
                excel_file_var.set(settings.get("excel_file", ""))
                sheet_name_var.set(settings.get("sheet_name", "Sheet1"))
                cell_range_var.set(settings.get("cell_range", "B1"))
                text_height_var.set(settings.get("text_height", "350"))
                direction_var.set(settings.get("direction", "vertical"))
                formula_info_var.set(settings.get("formula_info", ""))  # 新增：导入公式信息
                row_filter_var.set(settings.get("row_filter", "all"))   # 新增：导入行过滤选项
                messagebox.showinfo("成功", "设置已导入")
            except Exception as e:
                messagebox.showerror("错误", f"导入设置失败: {e}")

    # 在direction_var定义后添加
    formula_info_var = tk.StringVar()  # 新增：公式信息输入框变量
    row_filter_var = tk.StringVar(value="all")  # 新增：行过滤选项

    def load_file():
        """读取并缓存Excel数据"""
        # 清除之前的缓存
        cached_formulas.clear()
        
        excel_file = excel_file_var.get()
        sheet_name = sheet_name_var.get()
        cell_ranges = cell_range_var.get().split(',')

        if not excel_file:
            messagebox.showerror("错误", "请选择Excel文件")
            return

        def expand_range(cell_range):
            """将区域范围拆分为单行范围"""
            if ':' not in cell_range:
                return [cell_range]
            
            try:
                # 解析区域范围
                start_cell, end_cell = cell_range.split(':')
                start_col = re.match(r'([A-Za-z]+)', start_cell).group(1)
                start_row = int(re.match(r'[A-Za-z]+(\d+)', start_cell).group(1))
                end_col = re.match(r'([A-Za-z]+)', end_cell).group(1)
                end_row = int(re.match(r'[A-Za-z]+(\d+)', end_cell).group(1))
                
                # 生成单行范围列表
                expanded_ranges = []
                for row in range(start_row, end_row + 1):
                    # 根据过滤选项决定是否包含该行
                    filter_type = row_filter_var.get()
                    if filter_type == "all" or \
                       (filter_type == "odd" and row % 2 == 1) or \
                       (filter_type == "even" and row % 2 == 0):
                        expanded_ranges.append(f"{start_col}{row}:{end_col}{row}")
                return expanded_ranges
            except Exception as e:
                print(f"解析区域范围 {cell_range} 失败: {e}")
                return [cell_range]

        try:
            # 处理所有范围，包括区域范围
            processed_ranges = []
            for cell_range in cell_ranges:
                cell_range = cell_range.strip()
                if not cell_range:
                    continue
                
                # 扩展区域范围
                expanded = expand_range(cell_range)
                processed_ranges.extend(expanded)
            
            # 读取每个范围的数据
            for cell_range in processed_ranges:
                formulas = read_excel_formulas(excel_file, sheet_name, cell_range)
                if formulas:
                    cached_formulas[cell_range] = formulas
                else:
                    messagebox.showerror("错误", f"从 Excel 读取范围 {cell_range} 公式失败")
                    return
    
            messagebox.showinfo("成功", "Excel数据读取完成，可以点击插入公式")
        except Exception as e:
            messagebox.showerror("错误", f"读取Excel数据失败: {e}")

    def run_script():
        """插入已缓存的公式到CAD"""
        # 处理中文和英文逗号
        cell_ranges = [r.strip() for r in re.split(r'[,，]', cell_range_var.get()) if r.strip()]
        
        text_height = float(text_height_var.get())
        direction = direction_var.get()
    
        cad = connect_to_cad()
        if not cad:
            messagebox.showerror("错误", "连接 AutoCAD 失败")
            return
        doc = get_active_document(cad)
        if not doc:
            messagebox.showerror("错误", "获取活动文档失败")
            return
    
        try:
            # 处理所有缓存的范围（包括区域范围拆分后的单行范围）
            for cell_range in cached_formulas.keys():
                if not cell_range:
                    continue
                
                # 从缓存中获取首个单元格内容作为提示词
                first_formula = cached_formulas[cell_range][0] if cached_formulas[cell_range] else ""
                if ":" in first_formula:
                    above_cell, cell_content = first_formula.split(":", 1)
                    remark = f"{above_cell}→{cell_content.split('=')[0] if '=' in cell_content else cell_content}"
                else:
                    remark = cell_range
                        
                insertion_point = get_user_selected_point(doc, cell_range, remark)
                if not insertion_point:
                    messagebox.showwarning("警告", f"跳过范围 {cell_range}，无法获取插入点")
                    continue
    
                insertion_point_x = insertion_point[0]
                insertion_point_y = insertion_point[1]
    
                formulas = cached_formulas[cell_range]
                for i, formula in enumerate(formulas):
                    if direction == "vertical":
                        y = insertion_point_y - i * text_height * 2
                        x = insertion_point_x
                    else:
                        x = insertion_point_x + i * text_height * 10
                        y = insertion_point_y
                    
                    insertion_point = win32com.client.VARIANT(pythoncom.VT_ARRAY | pythoncom.VT_R8, (x, y, 0))
                    insert_text_to_cad(doc, formula, insertion_point, text_height)
                    time.sleep(0.1)
    
            messagebox.showinfo("完成", "所有公式已成功写入 CAD")
        except Exception as e:
            messagebox.showerror("错误", f"处理过程中出错: {e}")
        finally:
            cleanup_excel_resources()
    def export_to_txt():
        """将缓存的公式导出为TXT文件"""
        if not cached_formulas:
            messagebox.showerror("错误", "没有可导出的公式数据，请先加载Excel文件")
            return
        
        file_path = filedialog.asksaveasfilename(
            defaultextension=".txt",
            filetypes=[("Text files", "*.txt")],
            title="保存公式为TXT文件"
        )
        
        if not file_path:
            return
        
        try:
            with open(file_path, 'w', encoding='utf-8') as f:
                for cell_range, formulas in cached_formulas.items():
                    f.write(f"=== 范围: {cell_range} ===\n")
                    f.write("\n".join(formulas))
                    f.write("\n\n")
            messagebox.showinfo("成功", f"公式已成功导出到:\n{file_path}")
        except Exception as e:
            messagebox.showerror("错误", f"导出失败: {e}")

    # 创建 GUI 组件
    tk.Label(root, text="Excel 文件路径:").grid(row=0, column=0, sticky='e')
    tk.Entry(root, textvariable=excel_file_var, width=35).grid(row=0, column=1)
    tk.Button(root, text="选择文件", command=select_excel_file).grid(row=0, column=2)

    tk.Label(root, text="工作表名称:").grid(row=1, column=0, sticky='e')
    tk.Entry(root, textvariable=sheet_name_var).grid(row=1, column=1)

    tk.Label(root, text="公式范围:").grid(row=2, column=0, sticky='e')
    tk.Entry(root, textvariable=cell_range_var).grid(row=2, column=1)

    # 在排列方向组件后添加行过滤组件
    tk.Label(root, text="输出规则:").grid(row=3, column=0, sticky='e')
    tk.Radiobutton(root, text="全部", variable=row_filter_var, value="all").grid(row=3, column=1, sticky='w')
    tk.Radiobutton(root, text="奇数行", variable=row_filter_var, value="odd").grid(row=3, column=1)
    tk.Radiobutton(root, text="偶数行", variable=row_filter_var, value="even").grid(row=3, column=1, sticky='e')


    tk.Label(root, text="文字高度:").grid(row=4, column=0, sticky='e')
    tk.Entry(root, textvariable=text_height_var).grid(row=4, column=1)

        # 在行过滤组件后添加公式信息输入框
    # 新增：回溯模式变量（在formula_info_var定义后添加）
    traceback_mode_var = tk.StringVar(value="previous")  # 默认仅前一个单元格
    tk.Label(root, text="公式提示词:").grid(row=5, column=0, sticky='e')
    tk.Entry(root, textvariable=formula_info_var).grid(row=5, column=1)

    # 新增：回溯模式组件（行6）
    tk.Label(root, text="公式回溯模式:").grid(row=6, column=0, sticky='e')
    tk.Radiobutton(root, text="仅前一个单元格", variable=traceback_mode_var, value="previous").grid(row=6, column=1, sticky='w')
    tk.Radiobutton(root, text="回溯所有单元格", variable=traceback_mode_var, value="all").grid(row=6, column=1, sticky='e')

    # 原排列方向行（行6）调整为行7
    tk.Label(root, text="CAD写入方向:").grid(row=7, column=0, sticky='e')
    tk.Radiobutton(root, text="竖向", variable=direction_var, value="vertical").grid(row=7, column=1, sticky='w')
    tk.Radiobutton(root, text="横向", variable=direction_var, value="horizontal").grid(row=7, column=1, sticky='e')

    # 按钮框架行号调整为行8（原行7）
    button_frame = tk.Frame(root)
    button_frame.grid(row=8, column=0, columnspan=3, pady=10)

    tk.Button(button_frame, text="读取文件", command=load_file, width=10).pack(side='left', padx=5)
    tk.Button(button_frame, text="插入公式", command=run_script, width=10).pack(side='left', padx=5)
    tk.Button(button_frame, text="导出设置", command=export_settings, width=10).pack(side='left', padx=5)
    tk.Button(button_frame, text="导入设置", command=import_settings, width=10).pack(side='left', padx=5)  
    tk.Button(button_frame, text="公式导出", command=export_to_txt, width=10).pack(side='left', padx=5)



    root.protocol("WM_DELETE_WINDOW", lambda: [cleanup_excel_resources(), root.destroy()])
    root.mainloop()

if __name__ == "__main__":
    main()

